package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;


import model.User;

public class UserDAO extends ModelDAO {

	private Connection connection;
	
	public UserDAO (){
		super();
		connection = getConnection();
	}
	
	public void closeConnection(){
		super.closeConnection();
	}
	
	public User select(String user) {		
		User u = null;
		try{
			String consulta = "SELECT * FROM USERS WHERE USERNAME=?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			
			try{statement.setString(1,user);}
			catch(Exception e){e.printStackTrace();}
			
			ResultSet result = statement.executeQuery();
			while(result.next()){
				u = new User();
				u.setUsername(result.getString(1));
				u.setPassword(result.getString(2));
				u.setActive(result.getInt(3));
				u.setFirstName(result.getString(4));
				u.setLastName(result.getString(5));
				u.setAddress(result.getString(6));
				u.setPhone(result.getString(7));
				u.setModified(result.getTimestamp(8));
				u.setCreated(result.getTimestamp(9));
			}			
			result.close();
			statement.close();
		}
		catch(SQLException e){e.printStackTrace();}		
		return u;
	}
	
	public void create(User u){
		if(select(u.getUsername()) != null) return;
		
		try{
			String consulta = "INSERT INTO USERS "
				+"(USERNAME, ACTIVE, ADDRESS, FIRST_NAME, LAST_NAME, PASSWORD, PHONE, CREATED, MODIFIED)"
				+ "VALUES " + "(?,?,?,?,?,?,?,?,?)";
			PreparedStatement statement = this.connection.prepareStatement(consulta);
			
			try{statement.setString(1, u.getUsername());}
			catch(Exception e){e.printStackTrace();}

			try{statement.setInt(2, u.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			if(u.getAddress()!=null)statement.setString(3, u.getAddress());
			else {statement.setNull(3, java.sql.Types.VARCHAR);}
			
			if(u.getFirstName()!=null)statement.setString(4, u.getFirstName());
			else {statement.setNull(4, java.sql.Types.VARCHAR);}
			
			if(u.getLastName()!=null)statement.setString(5, u.getLastName());
			else {statement.setNull(5, java.sql.Types.VARCHAR);}
			
			if(u.getPassword()!=null)statement.setString(6, u.getPassword());
			else {statement.setNull(6, java.sql.Types.VARCHAR);}
			
			if(u.getPhone()!=null)statement.setString(7, u.getPhone());
			else {statement.setNull(7, java.sql.Types.VARCHAR);}
			
			if(u.getCreated()!=null)statement.setTimestamp(8, u.getCreated());
			else {statement.setNull(8, java.sql.Types.VARCHAR);}
			
			if(u.getModified()!=null)statement.setTimestamp(9, u.getModified());
			else {statement.setNull(9, java.sql.Types.VARCHAR);}
			
		    statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}	
	}

	public void update(User u){
		if(select(u.getUsername()) != null) return;
		
		try{
			String consulta = "UPDATE USERS "+
				"SET ACTIVE = ?, ADDRESS = ?, FIRST_NAME = ?,"+
				" LAST_NAME = ?, PASSWORD = ?, PHONE = ?, CREATED = ?, MODIFIED = ?"+
				" WHERE USERNAME = ?";
			PreparedStatement statement = this.connection.prepareStatement(consulta);

			try{statement.setInt(1, u.getActive());}
			catch(Exception e){e.printStackTrace();}
			
			if(u.getAddress()!=null)statement.setString(2, u.getAddress());
			else {statement.setNull(2, java.sql.Types.VARCHAR);}
			
			if(u.getFirstName()!=null)statement.setString(3, u.getFirstName());
			else {statement.setNull(3, java.sql.Types.VARCHAR);}
			
			if(u.getLastName()!=null)statement.setString(4, u.getLastName());
			else {statement.setNull(4, java.sql.Types.VARCHAR);}
			
			if(u.getPassword()!=null)statement.setString(5, u.getPassword());
			else {statement.setNull(5, java.sql.Types.VARCHAR);}
			
			if(u.getPhone()!=null)statement.setString(6, u.getPhone());
			else {statement.setNull(6, java.sql.Types.VARCHAR);}
			
			if(u.getCreated()!=null)statement.setTimestamp(7, u.getCreated());
			else {statement.setNull(7, java.sql.Types.VARCHAR);}
			
			if(u.getModified()!=null)statement.setTimestamp(8, u.getModified());
			else {statement.setNull(8, java.sql.Types.VARCHAR);}
			
			try{statement.setString(9, u.getUsername());}
			catch(Exception e){e.printStackTrace();}
			
			statement.executeUpdate();
		    statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
	}
	
	public List<User> getAll(){
		List<User> users = new ArrayList<User>();
		try{
			Statement statement = this.connection.createStatement();
			String consulta = "SELECT * FROM USERS";
			ResultSet results = statement.executeQuery(consulta);
			while(results.next()){
				User u = new User();
				u.setUsername(results.getString(1));
				u.setPassword(results.getString(2));
				u.setActive(results.getInt(3));
				u.setFirstName(results.getString(4));
				u.setLastName(results.getString(5));
				u.setAddress(results.getString(6));
				u.setPhone(results.getString(7));
				u.setModified(results.getTimestamp(8));
				u.setCreated(results.getTimestamp(9));
				if(u.getActive()==1)
					users.add(u);
			}		
			results.close();
			statement.close();
		}
		catch(SQLException e){e.printStackTrace();}
		return users;
	}
	
	public void delete(String user){
		User u = select(user);
		u.setActive(0);
		update(u);
	}
}
